"""
本模块定义了API路径路由
"""
import os
from json import loads, dumps

from package.db import DB
from package.utils import *

from flask_mqtt import Mqtt
from flask import jsonify, request, g, current_app

def init(app, DB: DB):
    # mqtt.subscribe('/iot/login')

    mqtt = Mqtt(app)
    # @mqtt.on_connect()
    # def handle_connect(client, userdata, flags, rc):
    #     mqtt.subscribe('/iot/login')

    @app.route("/iot/mqttlogin/<string:client_id>", methods=['POST'])
    def handle_mqtt_message(client_id):
        conn = DB.get_db_conn()
        def get_sensor():
            sql = "SELECT device_id, device_type, threshold_max, threshold_min FROM devices WHERE device_name = %s"
            cursor.execute(sql, (client_id))
            device = cursor.fetchall()
            return device
        try:
            with conn.cursor() as cursor:
                device = get_sensor()
                # print(dumps(ans, default=str))
                # return ans_builder()
                if not device:
                    sql = "INSERT INTO devices (device_name, device_type) VALUES (%s, %s)"
                    for each in ["temp", "humAir", "humSoil", "ill"]:
                        cursor.execute(sql, (client_id, each))
                    conn.commit()
                    device = get_sensor()
                ans = {"client_id": client_id}
                ans.update({"sensors": device})
                
                mqtt.publish(topic="/iot/login", payload=dumps(ans,default=str))
                return jsonify(ans)

        except Exception as e:
            print(e)
            return ans_builder(UNKNOWN_ERROR, status=False, code=400)
        finally:
            conn.close()

    @app.route("/iot")
    def hello_world():
        return f"<p>Welcome</p>"

    @app.teardown_appcontext
    def close_connection(exception):
        '''确保每次请求结束后关闭数据库连接'''
        db = getattr(g, '_database', None)
        if db is not None:
            current_app.logger.error("DB error exit")
            db.close()

    @app.route('/iot/test_db', methods=['GET','POST'])
    def test_connect():
        '''测试数据库连接'''
        return jsonify(DB.test_connect())

    """设备部分"""

    # 增加设备
    @app.route('/iot/devices', methods=['POST'])
    def add_device():
        """
        增加设备API的路由

        Parameters:
            device_name (str): 设备名称.
            device_type (str): 设备类型.

        Returns:
            e.g: {"device_id": 1, "status": "success"}.
        """
        conn = DB.get_db_conn()
        try:
            with conn.cursor() as cursor:
                sql = "INSERT INTO devices (device_name, device_type,"+\
                    " threshold_max, threshold_min, actuator_name) VALUES (%s, %s, %s, %s, %s)"
                device_name = request.json.get('device_name')
                device_type = request.json.get('device_type')
                threshold_max = request.json.get('threshold_max', 999)
                threshold_min = request.json.get('threshold_min', 0)
                actuator_name = request.json.get('actuator_name', None)
                param = (device_name, device_type, threshold_max, threshold_min, actuator_name)
                cursor.execute(sql, param)
                conn.commit()
                return ans_builder({"device_id":cursor.lastrowid}, code=201)

        except Exception as e:
            current_app.logger.error(e)
            return ans_builder(PARAM_ERROR, status=False, code=400)
        finally:
            conn.close()

    # 查询所有设备
    @app.route('/iot/devices', methods=['GET'])
    def get_all_devices():
        """
        查询所有设备信息,包括表中每个字段

        Parameters:
            None

        Returns:
            e.g:[{
                "actuator_name": null,
                "actuator_status": null,
                "device_id": 0,
                "device_name": "temp",
                "device_type": "temp",
                "last_update": null,
                "status": null,
                "threshold_max": null,
                "threshold_min": null
                }]
        """
        conn = DB.get_db_conn()
        try:
            with conn.cursor() as cursor:
                cursor.execute("SELECT * FROM devices")
                devices = cursor.fetchall()
                return jsonify(devices)

        except Exception as e:
            current_app.logger.error(e)
            return ans_builder(UNKNOWN_ERROR, status=False, code=400)
        finally:
            conn.close()

    # 查询所有设备类型以及ID
    @app.route('/iot/devices/type', methods=['GET'])
    def get_all_devices_type():
        """
        查询所有设备类型,按照类型返回设备ID

        Parameters:
            None

        Returns:
            e.g:[{
                "device_type": "DeviceType",
                "ids": [0,1,2,3]
                }]
        """
        conn = DB.get_db_conn()
        try:
            with conn.cursor() as cursor:
                res = []
                cursor.execute("SELECT DISTINCT device_type FROM devices")
                types = cursor.fetchall()
                cursor.execute("SELECT device_id, device_type FROM devices")
                devices = cursor.fetchall()
                # 将查询到的设备id根据类型分类
                for each in types:
                    tmp = []
                    for one in devices:
                        if one['device_type'] == each['device_type']:
                            tmp.append(one['device_id'])
                    res.append({"device_type": each['device_type'], "ids": tmp})
                return jsonify(res)

        except Exception as e:
            current_app.logger.error(e)
            return ans_builder(UNKNOWN_ERROR, status=False, code=400)
        finally:
            conn.close()

    #根据设备名称获取设备ID
    @app.route('/iot/devices/<string:device_name>', methods=['GET'])
    def get_device_by_name(device_name):
        """
        根据设备名称获取名称下的所有ID

        Parameters:
            device_name (str): 设备名.

        Returns:
            e.g: {
        """
        conn = DB.get_db_conn()
        try:
            with conn.cursor() as cursor:
                cursor.execute("SELECT device_id, device_type FROM devices WHERE device_name = %s", (device_name))
                device = cursor.fetchall()
                if device:
                    return jsonify(device)
                else:
                    return ans_builder(NOTFOUND_ERROR, status=False, code=404)

        except Exception as e:
            current_app.logger.error(e)
            return ans_builder(UNKNOWN_ERROR, status=False, code=400)
        finally:
            conn.close()

    # 按ID查询单个设备
    @app.route('/iot/devices/<int:device_id>', methods=['GET'])
    def get_device_by_id(device_id):
        """
        根据设备ID查询设备所有信息

        Parameters:
            device_id (str): 设备ID.

        Returns:
            e.g: {
                "actuator_name": "actuator0",
                "actuator_status": 0,
                "device_id": 1,
                "device_name": "device0",
                "device_type": "devicetype",
                "last_update": "Thu, 13 Jun 2024 10:06:26 GMT",
                "status": 1,
                "threshold_max": "0.00",
                "threshold_min": "0.00"
            }
        """
        conn = DB.get_db_conn()
        try:
            with conn.cursor() as cursor:
                cursor.execute("SELECT * FROM devices WHERE device_id = %s", (device_id))
                device = cursor.fetchone()
                if device:
                    return jsonify(device)
                else:
                    return ans_builder(NOTFOUND_ERROR, status=False, code=404)

        except Exception as e:
            current_app.logger.error(e)
            return ans_builder(UNKNOWN_ERROR, status=False, code=400)
        finally:
            conn.close()

    # 更新设备信息
    @app.route('/iot/devices/<int:device_id>', methods=['PUT'])
    def update_device(device_id):
        """
        根据参数更新设备信息

        Parameters:
            device_name (str): 设备名称
            device_type (str): 设备类型
            status (bool): 设备在线状态
            threshold_max (int) : 报警阈值上限
            threshold_min (int): 报警阈值下限
            actuator_name (str): 关联执行器名称
            actuator_status (bool): 关联执行器在线状态

        Returns:
            e.g: {"message": "Device updated", "status": "success"}.
        """
        conn = DB.get_db_conn()
        try:
            with conn.cursor() as cursor:
                sql = "UPDATE devices SET device_name = %s, "+\
                        "device_type = %s, "+\
                        "status = %s, "+\
                        "threshold_max = %s, "+\
                        "threshold_min = %s, "+\
                        "actuator_name = %s, "+\
                        "actuator_status = %s "+\
                        "WHERE device_id = %s"
                param = (request.json.get('device_name', None),
                        request.json.get('device_type', None),
                        request.json.get('status', None),
                        request.json.get('threshold_max', None),
                        request.json.get('threshold_min', None),
                        request.json.get('actuator_name', None),
                        request.json.get('actuator_status', None),
                        device_id)
                cursor.execute(sql, param)
                conn.commit()
                if cursor.rowcount > 0:
                    return ans_builder({"message": "Device updated"})
                else:
                    return ans_builder(NOTFOUND_ERROR, status=False, code=404)

        except Exception as e:
            current_app.logger.error(e)
            return ans_builder(UNKNOWN_ERROR, status=False, code=400)
        finally:
            conn.close()

    # 删除设备
    @app.route('/iot/devices/<int:device_id>', methods=['DELETE'])
    def delete_device(device_id):
        """
        根据设备ID删除指定设备

        Parameters:
            None

        Returns:
            e.g: {"message": "Device updated", "status": "success"}.
        """
        conn = DB.get_db_conn()
        try:
            with conn.cursor() as cursor:
                cursor.execute("DELETE FROM devices WHERE device_id = %s", (device_id))
                conn.commit()
                if cursor.rowcount > 0:
                    return ans_builder({"message": "Device deleted"})
                else:
                    return ans_builder(NOTFOUND_ERROR, status=False, code=404)

        except Exception as e:
            current_app.logger.error(e)
            return ans_builder(UNKNOWN_ERROR, status=False, code=400)
        finally:
            conn.close()

    """传感器部分"""
    @app.route('/iot/sensor_value/<int:sensor_id>', methods=['GET'])
    def get_sensor_value(sensor_id):
        """
        根据设备ID查询最近N条上传的传感器数据

        Parameters:
            sensor_id (int): 传感器ID
            num (int): 数据条数

        Returns:
            e.g: {"message": "Device updated", "status": "success"}.
        """
        conn = DB.get_db_conn()
        try:
            with conn.cursor() as cursor:
                sql = "SELECT id, value, time FROM sensor_value WHERE device_id = %s ORDER BY time DESC LIMIT %s "
                param = (sensor_id, request.args.get('num', 100, int))
                cursor.execute(sql, param)
                recent_values = cursor.fetchall()
                if recent_values:
                    return jsonify({"device_id": sensor_id, "datas": recent_values})
                else:
                    return ans_builder(NOTFOUND_ERROR, status=False, code=404)

        except Exception as e:
            current_app.logger.error(e)
            return ans_builder(UNKNOWN_ERROR, status=False, code=400)
        finally:
            conn.close()

    @app.route('/iot/sensor_value', methods=['POSt'])
    def set_sensor_value():
        """
        插入一条数据记录

        Parameters:
            device_id (int): 设备ID
            value (string): 传感器数据

        Returns:
            e.g: { "id": 1, "status": "success"}
        """
        con = DB.get_db_conn()
        try:
            with con.cursor() as cursor:
                sql = "INSERT INTO sensor_value (device_id, value) VALUES (%s, %s)"
                param = (request.json.get('device_id'), request.json.get('value'))
                cursor.execute(sql, param)
                con.commit()
                return ans_builder({"id": cursor.lastrowid}, code=201)

        except Exception as e:
            current_app.logger.error(e)
            return ans_builder(PARAM_ERROR, status=False, code=400)
        finally:
            con.close()

    """报警部分"""

    @app.route('/iot/alarm', methods=['GET', 'POST'])
    def get_alarm():
        """
        历史报警信息查询

        Parameters:
            num (int): 数据条数

        Returns:
            e.g: [
                {
                    "id": 2,
                    "sensor_name": "device01",
                    "timestamp": "Sun, 16 Jun 2024 14:35:45 GMT",
                    "value": "404.00"
                }
            ]
        """
        conn = DB.get_db_conn()
        try:
            with conn.cursor() as cursor:
                if request.method == "GET":
                    sql = "SELECT * FROM alarm_log ORDER BY `time` DESC LIMIT %s"
                    cursor.execute(sql, request.args.get('num', 100, int))
                    recent_values = cursor.fetchall()
                    return jsonify(recent_values)
                elif request.method == "POST":
                    sql = "INSERT INTO alarm_log (device_id, value) VALUES (%s, %s)"
                    device_id = request.json.get('device_id')
                    value = request.json.get('value')
                    cursor.execute(sql, (device_id, value))
                    # 邮件发送
                    if checkemail(os.environ['mailaddr']):
                        alarm_body = f"设备ID为{device_id}的设备数值超出阈值\n数值为{value}\n\
                            登录http://116.198.227.244/以处理该报警\n!请勿回复该邮件"
                        mail(os.environ['mailaddr'], ALARM_SUBJECT, alarm_body)
                    conn.commit()
                    return jsonify({"id":cursor.lastrowid})

        except Exception as e:
            current_app.logger.error(e)
            return ans_builder(UNKNOWN_ERROR, status=False, code=400)
        finally:
            conn.close()

    @app.route('/iot/alarm/stop', methods=['POST'])
    def stop_alarm():
        ans = {"actuator_name": "beep", "actuator_status": 0}
        mqtt.publish(topic="/iot/actuator", payload=dumps(ans))
        return ans_builder()

    @app.route('/iot/cmd', methods=['POST'])
    def cmd():
        clientid = request.json.get('client_id') #TODO:
        name = request.json.get("actuator_name")
        status = request.json.get("actuator_status")
        ans = {"client_id": clientid,"actuator_name": name, "actuator_status": status}
        mqtt.publish(topic="/actuator", payload=dumps(ans))
        conn = DB.get_db_conn()
        try:
            with conn.cursor() as cursor:
                sql = "UPDATE devices SET actuator_status = %s WHERE device_name = %s && actuator_name = %s"
                param = (status, clientid, name)
                cursor.execute(sql, param)
                conn.commit()

        except Exception as e:
            current_app.logger.error(e)
            return ans_builder(UNKNOWN_ERROR, status=False, code=400)
        finally:
            conn.close()
        return ans_builder(ans)

    """邮件管理部分"""
    @app.route('/iot/dstmailaddr', methods=['GET', 'POST'])
    def mail_handle():
        if request.method == 'POST':
            mail = request.args.get('addr')
            if checkemail(mail):
                os.environ['mailaddr'] = mail
                return ans_builder({"addr": mail})
            else:
                return ans_builder(PARAM_ERROR, status= False, code=400)
        return jsonify({"dstmailaddr": os.environ['mailaddr']})
    
    @app.route('/iot/weather', methods=['GET'])
    def weather():
        import requests
        
        url = "https://devapi.qweather.com/v7/weather/7d?location=101030400&key=01a2d315fcaa4800b7095cc4aacb543b"

        response = requests.request("GET", url)

        # print(response.text)
        return response.text